from routes import db  # 数据库实例
from models.gep_model import GepParameter, GepResult  # GEP 参数和结果模型
from sqlalchemy.exc import IntegrityError  # SQLAlchemy 异常处理


class GepService:
    def add_gep_parameter(self, data):
        try:
            gep_parameter = GepParameter(**data)  # 创建 GepParameter 实例
            db.session.add(gep_parameter)  # 添加到数据库会话
            db.session.commit()  # 提交更改
            return gep_parameter  # 返回创建的参数
        except IntegrityError:  # 捕获唯一性约束异常
            db.session.rollback()  # 回滚事务
            raise ValueError("Data violates unique constraints")  # 抛出错误


    def update_gep_parameter(self, id, data):
        try:
            gep_parameter = GepParameter.query.get(id)  # 获取指定 ID 的参数
            if gep_parameter:
                for key, value in data.items():  # 更新字段
                    setattr(gep_parameter, key, value)
                db.session.commit()  # 提交更改
                return gep_parameter  # 返回更新后的参数
            else:
                raise ValueError("GEP Parameter not found")  # 抛出未找到错误
        except IntegrityError:  # 捕获唯一性约束异常
            db.session.rollback()  # 回滚事务
            raise ValueError("Data violates unique constraints")  # 抛出错误


    def delete_gep_parameter(self, id):
        try:
            gep_parameter = GepParameter.query.get(id)  # 获取指定 ID 的参数
            if gep_parameter:
                db.session.delete(gep_parameter)  # 删除参数
                db.session.commit()  # 提交更改
                return True  # 返回成功
            else:
                raise ValueError("GEP Parameter not found")  # 抛出未找到错误
        except IntegrityError:  # 捕获完整性约束异常
            db.session.rollback()  # 回滚事务
            raise ValueError("Deletion failed")  # 抛出删除失败错误


    def get_gep_parameter(self, id):
        gep_parameter = GepParameter.query.get(id)  # 获取指定 ID 的参数
        if not gep_parameter:
            raise ValueError("GEP Parameter not found")  # 抛出未找到错误
        return gep_parameter  # 返回参数


    def get_all_gep_parameters(self):
        return GepParameter.query.all()

    def get_gep_parameter_id_by_fields(self, year, gep_city, gep_district, gep_town):
        from models.gep_model import GepParameter
        record = GepParameter.query.filter_by(
            year=year,
            gepCity=gep_city,
            gepDistrict=gep_district,
            gepTown=gep_town
        ).first()
        return record.id if record else None

    def get_gep_parameter_by_fields(self, year, gep_city, gep_district, gep_town):
        from models.gep_model import GepParameter
        # print("get_gep_parameter_by_fields", year, gep_city, gep_district, gep_town)
        # print(GepParameter.query.filter_by(
        #     year=year,
        #     gepCity=gep_city,
        #     gepDistrict=gep_district,
        #     gepTown=gep_town
        # ))
        return GepParameter.query.filter_by(
            year=year,
            gepCity=gep_city,
            gepDistrict=gep_district,
            gepTown=gep_town
        ).first()

    def update_gep_parameter_by_fields(self, year, gep_city, gep_district, gep_town, update_data):
        # 1. 查询是否存在该记录
        record = self.get_gep_parameter_by_fields(year, gep_city, gep_district, gep_town)

        if not record:
            # 2. 如果不存在，则创建新记录，并从 update_data 中提取所有合法字段
            record = GepParameter(
                year=year,
                gepCity=gep_city,
                gepDistrict=gep_district,
                gepTown=gep_town,
                **{k: v for k, v in update_data.items() if k not in ['year', 'gepCity', 'gepDistrict', 'gepTown']}
            )
        else:
            # 3. 如果存在，则仅更新传入的字段（避免覆盖已有数据）
            for key, value in update_data.items():
                setattr(record, key, value)

        # 4. 提交更改或新增
        db.session.add(record)
        db.session.commit()
        return record

    def delete_gep_parameter_by_fields(self, year, gep_city, gep_district, gep_town):
        record = self.get_gep_parameter_id_by_fields(year, gep_city, gep_district, gep_town)
        if not record:
            return False
        db.session.delete(record)
        db.session.commit()
        return True




    # GepResult 的增删改查逻辑
    def add_gep_result(self,data):
        gep_result = GepResult(**data)
        db.session.add(gep_result)
        db.session.commit()
        return gep_result

    def update_gep_result(self,id, data):
        gep_result = GepResult.query.get(id)
        if gep_result:
            for key, value in data.items():
                setattr(gep_result, key, value)
            db.session.commit()
            return gep_result
        return None

    def delete_gep_result(self,id):
        gep_result = GepResult.query.get(id)
        if gep_result:
            db.session.delete(gep_result)
            db.session.commit()
            return True
        return False

    def get_gep_result(self,id):
        return GepResult.query.get(id)

    def get_all_gep_results(self):
        return GepResult.query.all()

    def get_gep_result_id_by_fields(self, year, gep_city, gep_district, gep_town):
        from models.gep_model import GepResult
        record = GepResult.query.filter_by(
            year=year,
            gepCity=gep_city,
            gepDistrict=gep_district,
            gepTown=gep_town
        ).first()
        return record.id if record else None

    def get_gep_result_by_fields(self, year, gep_city, gep_district, gep_town):
        from models.gep_model import GepResult
        return GepResult.query.filter_by(
            year=year,
            gepCity=gep_city,
            gepDistrict=gep_district,
            gepTown=gep_town
        ).first()

    def update_gep_result_by_fields(self, year, gep_city, gep_district, gep_town, update_data):
        # 1. 查询是否存在该记录
        record = self.get_gep_result_by_fields(year, gep_city, gep_district, gep_town)

        if not record:
            # 2. 如果不存在，则创建新记录，并从 data 中提取所有合法字段
            record = GepResult(
                year=year,
                gepCity=gep_city,
                gepDistrict=gep_district,
                gepTown=gep_town,
                **{k: v for k, v in update_data.items() if k not in ['year', 'gepCity', 'gepDistrict', 'gepTown']}
            )
        else:
            # 3. 如果存在，则仅更新传入的字段（避免覆盖已有数据）
            for key, value in update_data.items():
                setattr(record, key, value)

        # 4. 提交更改或新增
        db.session.add(record)
        db.session.commit()
        return record


    def delete_gep_result_by_fields(self, year, gep_city, gep_district, gep_town):
        record = self.get_gep_result_id_by_fields(year, gep_city, gep_district, gep_town)
        if not record:
            return False
        db.session.delete(record)
        db.session.commit()
        return True
